package com.ymt.fmd.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Logger;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.io.output.DeferredFileOutputStream;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.monitorjbl.xlsx.StreamingReader;

@SuppressWarnings({ "rawtypes", "deprecation", "unchecked", "unused" })
public class ExcelUtil {
	
	private static Logger logger = Logger.getLogger(ExcelUtil.class.getName());

	/**
	 * 导出数据
	 * 
	 * @param os
	 * @param data
	 * @throws IOException
	 */
	public static void exportToExcel(OutputStream os, List<List<String>> data,
			List<String> headList) throws IOException {
		HSSFWorkbook wb = null;
		try {
			// 创建工作簿
			wb = new HSSFWorkbook();
			// 创建表
			HSSFSheet sheet = wb.createSheet("Data");
			// 创建Excel表的第一行即表头
			Row row0 = sheet.createRow(0);
			for (int i = 0; i < headList.size(); i++) {
				Cell headCell = row0.createCell(i);
				headCell.setCellType(Cell.CELL_TYPE_STRING);
				headCell.setCellStyle(getTitleStyle(wb, sheet));
				headCell.setCellValue(String.valueOf(headList.get(i)));

			}
			// 创建数据行
			for (int r = 0; r < data.size(); r++) {
				// 创建第r+1行
				HSSFCellStyle style = getCellStyle(wb);
				HSSFRow row = sheet.createRow(r + 1);

				List<String> cols = data.get(r);
				for (int c = 0; c < cols.size(); c++) {
					// 创建第c个单元格
					HSSFCell cell = row.createCell(c);
					cell.setCellStyle(style);
					String oj = "";
					if (cols.get(c) != "" && null != cols.get(c)) {
						oj = cols.get(c).toString();
					}
					cell.setCellValue(new HSSFRichTextString(oj));
				}
			}
			// 隐藏列
			// sheet.setColumnHidden(1,true);
			// 自适应宽度
			sheet.autoSizeColumn(1, true); // 只对数字和英文有效
			// 隐藏行
			// row.setZeroHeight(true);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (wb != null) {
				wb.write(os);
			}
			os.flush();
			os.close();
		}
	}

	public void exportAll(String[] tableHeader, List<Integer> elist,
			JSONObject map, List<List<String>> data, OutputStream os)
			throws Exception {
		HSSFWorkbook wb = null;
		try {
			// 创建工作簿
			wb = new HSSFWorkbook();
			// 创建表
			HSSFSheet sheet = wb.createSheet("Data");

			HSSFRow headerRow = sheet.createRow((short) 0);
			for (int i = 0; i < (short) tableHeader.length; i++) {
				HSSFCell headerCell = headerRow.createCell((short) i);
				headerCell.setCellValue(tableHeader[i]);
				headerCell.setCellStyle(getTitleStyle(wb, sheet));
			}

			// 创建数据行
			if (data != null) {
				for (int r = 0; r < data.size(); r++) {
					// 创建第r+1行
					HSSFRow row = sheet.createRow(r + 1);
					HSSFCellStyle style = getCellStyle(wb);
					List<String> cols = data.get(r);
					for (int c = 0; c < cols.size(); c++) {
						// 创建第c个单元格
						HSSFCell cell = row.createCell(c);
						cell.setCellStyle(style);

						String oj = "";
						if (cols.get(c) != null) {
							oj = cols.get(c).toString();
						}
						JSONArray lsList = map.getJSONArray(String.valueOf(c));
						if (lsList != null) {
							String[] enuData = new String[] {};
							enuData = (String[]) lsList.toArray(enuData);
							for (String aString : enuData) {
								String[] vals = aString.split("-");
								if (oj.equalsIgnoreCase(vals[0])) {
									oj = aString;
								}
							}
							// 选定区域
							CellRangeAddressList regions = new CellRangeAddressList(
									0, 65535, c, c);
							// 生成下拉框内容
							DVConstraint constraint = DVConstraint
									.createExplicitListConstraint(enuData);
							// 绑定下拉框和作用区域
							HSSFDataValidation data_validation = new HSSFDataValidation(
									regions, constraint);
							// 对sheet页生效
							sheet.addValidationData(data_validation);
						}
						cell.setCellValue(new HSSFRichTextString(oj));
					}
				}
			} else { // 数据为空
				if (map != null) { // 添加下拉框设置
					HSSFRow row = sheet.createRow(1); // 创建数据行
					HSSFCellStyle style = getCellStyle(wb);
					for (int c = 0; c < tableHeader.length; c++) {
						// 创建第c个单元格
						HSSFCell cell = row.createCell(c);
						cell.setCellStyle(style);

						JSONArray lsList = map.getJSONArray(String.valueOf(c));
						if (lsList != null) {
							String[] enuData = new String[] {};
							enuData = (String[]) lsList.toArray(enuData);
							// 选定区域
							CellRangeAddressList regions = new CellRangeAddressList(
									0, 65535, c, c);
							// 生成下拉框内容
							DVConstraint constraint = DVConstraint
									.createExplicitListConstraint(enuData);
							// 绑定下拉框和作用区域
							HSSFDataValidation data_validation = new HSSFDataValidation(
									regions, constraint);
							// 对sheet页生效
							sheet.addValidationData(data_validation);
						}
					}
				}
			}
			sheet.setGridsPrinted(true);
			HSSFFooter footer = sheet.getFooter();
			footer.setRight("Page " + HSSFFooter.page() + " of "
					+ HSSFFooter.numPages());
			// tableSheet.setColumnHidden(0,true); //隐藏ID列
			// wb.write(os);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (wb != null) {
				wb.write(os);
			}
			os.flush();
			os.close();
		}

	}

	/**
	 * 03数据导入
	 * 
	 * @param is
	 * @param model
	 *            模型名称
	 * @param tlist
	 *            表头及数据集List
	 * @param etypeList
	 *            单元格编辑类型List
	 * @return
	 * @throws IOException
	 */
	/*
	 * CELL_TYPE_NUMERIC 数值型 0 CELL_TYPE_STRING 字符串型 1 CELL_TYPE_FORMULA 公式型 2
	 * CELL_TYPE_BLANK 空值 3 CELL_TYPE_BOOLEAN 布尔型 4 CELL_TYPE_ERROR 错误 5
	 */
	public static List<List<String>> importFromExcel(InputStream is,
			String model, List<String> tlist, List<String> etypeList)
			throws IOException {
		// Workbook wb = StreamingReader.builder()
		// .rowCacheSize(100) // number of rows to keep in memory (defaults to
		// 10)
		// .bufferSize(1024) // buffer size to use when reading InputStream to
		// file (defaults to 1024)
		// .open(f);
		HSSFWorkbook wb = new HSSFWorkbook(is);
		// HSSFSheet sheet = wb.getSheetAt(0);
		Sheet sheet = wb.getSheetAt(0);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		List<List<String>> data = new ArrayList<List<String>>();
		int cellTotal = sheet.getRow(sheet.getFirstRowNum())
				.getPhysicalNumberOfCells();

		// 解决隐藏列的问题
		List<Integer> orderList = new ArrayList<Integer>();
		for (int j = 0; j < tlist.size(); j++) {
			int num = 0;
			for (int i = 0; i < cellTotal; i++) {
				Cell hCell = sheet.getRow(0).getCell(i);
				if ((hCell.toString()).equals(tlist.get(j))) {
					num = 1;
				}
			}
			if (num == 0) { // 说明是隐藏列
				orderList.add(j);
			}
		}

		try {
			int fr = sheet.getFirstRowNum(), lr = sheet.getLastRowNum();
			for (int r = fr; r <= lr; r++) {
				Row row = sheet.getRow(r);
				if (row == null) {
					continue;
				}
				List<String> cols = new ArrayList<String>();
				for (int c = 0; c < cellTotal; c++) { // row.getFirstCellNum()拿不到前边几列为空的索引
														// row.getLastCellNum()
														// 拿不到最后一列为空时
					logger.finer("importFromExcel cell index="+c);
					if (orderList.contains(c)) {
						cols.add(c, "");
					}
					Cell cell = row.getCell(c); // 导入的表头+数据
					if (cell == null) {
						cols.add("");
						// cols.add("''");
					} else {
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_STRING:
							logger.finer("importFromExcel cell col type="+etypeList.get(c));
							if (etypeList.get(c).equals("coro") && r != 0) {
								String ss = cell.getStringCellValue();
								int idxUl = ss.indexOf("-");
								String v1 = ss;
								if (idxUl >= 0) {
									v1 = ss.substring(0, idxUl);
								}
								cols.add("\"" + v1 + "\"");
							} else {
								cols.add("\"" + cell.getStringCellValue()
										+ "\"");
							}
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							cols.add("\"" + cell.getBooleanCellValue() + "\"");
							break;
						case HSSFCell.CELL_TYPE_NUMERIC:
							// DecimalFormat df = new DecimalFormat("0");
							// String strCell =
							// df.format(cell.getNumericCellValue());
							// break;
							if (org.apache.poi.ss.usermodel.DateUtil
									.isCellDateFormatted(cell)) {
								cols.add("\""
										+ sdf.format(cell.getDateCellValue())
										+ "\"");
							} else {
								cols.add("\""
										+ new DecimalFormat("#.######")
												.format(cell
														.getNumericCellValue())
										+ "\"");
							}
							break;
						case HSSFCell.CELL_TYPE_FORMULA: // 公式类型
							cols.add("\"" + cell.getCellFormula() + "\"");
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							cols.add("");
							// cols.add("''");
							break;
						}
					}
				}

				data.add(cols);
			}
		} finally {
			is.close();
		}
		return data;
	}

	/**
	 * 
	 * @param is
	 * @param firstCellNum
	 * @param lastCellNum
	 * @return
	 * @throws IOException
	 */
	public static List<List<String>> importFromExcelWithCell(InputStream is,
			int firstCellNum, int lastCellNum) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook(is);
		HSSFSheet sheet = wb.getSheetAt(0);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		List<List<String>> data = new ArrayList<List<String>>();
		try {
			for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
				HSSFRow row = sheet.getRow(r);
				if (row == null) {
					continue;
				}
				List<String> cols = new ArrayList<String>();
				for (int c = firstCellNum; c < lastCellNum; c++) {
					HSSFCell cell = row.getCell(c);

					if (cell == null) {
						cols.add("");
					} else {
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_STRING:
							cols.add(cell.getStringCellValue());
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							cols.add("" + cell.getBooleanCellValue());
							break;
						case HSSFCell.CELL_TYPE_NUMERIC:
							if (org.apache.poi.ss.usermodel.DateUtil
									.isCellDateFormatted(cell)) {
								cols.add(sdf.format(cell.getDateCellValue()));
							} else {
								cols.add("" + cell.getNumericCellValue());
							}

							break;
						case HSSFCell.CELL_TYPE_FORMULA:
							cols.add(cell.getCellFormula());
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							cols.add("");
							break;
						}
					}
				}

				data.add(cols);
			}
		} finally {
			is.close();
		}
		return data;
	}

	/**
	 * 该方法把num类型强转成了string
	 * 
	 * @param is
	 * @param firstCellNum
	 * @param lastCellNum
	 * @return
	 * @throws IOException
	 */
	public static List<List<String>> importFromExcelWithCellWithNUm(
			InputStream is, int firstCellNum, int lastCellNum)
			throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook(is);
		HSSFSheet sheet = wb.getSheetAt(0);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		DecimalFormat df = new DecimalFormat("#");
		List<List<String>> data = new ArrayList<List<String>>();
		try {
			for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
				HSSFRow row = sheet.getRow(r);
				if (row == null) {
					continue;
				}

				List<String> cols = new ArrayList<String>();
				for (int c = firstCellNum; c < lastCellNum; c++) {
					HSSFCell cell = row.getCell(c);
					if (cell == null) {
						cols.add("");
					} else {
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_STRING:
							cols.add(cell.getStringCellValue());
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							cols.add("" + cell.getBooleanCellValue());
							break;
						case HSSFCell.CELL_TYPE_NUMERIC:
							if (org.apache.poi.ss.usermodel.DateUtil
									.isCellDateFormatted(cell)) {
								cols.add(sdf.format(cell.getDateCellValue()));
							} else {

								cols.add(""
										+ df.format(cell.getNumericCellValue()));
								// cols.add(""+cell.getNumericCellValue());
							}

							break;
						case HSSFCell.CELL_TYPE_FORMULA:
							cols.add(cell.getCellFormula());
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							cols.add("");
							break;
						}
					}
				}

				data.add(cols);
			}
		} finally {
			is.close();
		}
		return data;
	}

	/***
	 * 该方法把导入excel时对于数字类型的数据进行处理 一般情况下excel中数字太长会转化成科学计数法
	 * 
	 * @param is
	 * @return
	 * @throws IOException
	 */
	public static List<List<String>> importExcelWithCellWithNum(InputStream is)
			throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook(is);
		HSSFSheet sheet = wb.getSheetAt(0);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		DecimalFormat df = new DecimalFormat("#");
		List<List<String>> data = new ArrayList<List<String>>();
		try {
			for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
				HSSFRow row = sheet.getRow(r);
				if (row == null) {
					continue;
				}

				List<String> cols = new ArrayList<String>();
				for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
					HSSFCell cell = row.getCell(c);
					if (cell == null) {
						cols.add("");
					} else {
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_STRING:
							cols.add(cell.getStringCellValue());
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							cols.add("" + cell.getBooleanCellValue());
							break;
						case HSSFCell.CELL_TYPE_NUMERIC:
							if (org.apache.poi.ss.usermodel.DateUtil
									.isCellDateFormatted(cell)) {
								cols.add(sdf.format(cell.getDateCellValue()));
							} else {
								cols.add(""
										+ df.format(cell.getNumericCellValue()));
							}
							break;
						case HSSFCell.CELL_TYPE_FORMULA:
							cols.add(cell.getCellFormula());
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							cols.add("");
							break;
						}
					}
				}

				data.add(cols);
			}
		} finally {
			is.close();
		}
		return data;
	}

	public static List<List<String>> importExcelWithNullCell(InputStream is)
			throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook(is);
		HSSFSheet sheet = wb.getSheetAt(0);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		DecimalFormat df = new DecimalFormat("#");
		List<List<String>> data = new ArrayList<List<String>>();

		try {
			int lastCellNum = sheet.getRow(sheet.getFirstRowNum())
					.getLastCellNum();

			for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
				HSSFRow row = sheet.getRow(r);
				if (row == null) {
					continue;
				}

				List<String> cols = new ArrayList<String>();
				for (int c = 0; c < lastCellNum; c++) {
					HSSFCell cell = row.getCell(c);
					if (cell == null) {
						cols.add("");
					} else {
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_STRING:
							cols.add(cell.getStringCellValue());
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							cols.add("" + cell.getBooleanCellValue());
							break;
						case HSSFCell.CELL_TYPE_NUMERIC:
							if (org.apache.poi.ss.usermodel.DateUtil
									.isCellDateFormatted(cell)) {
								cols.add(sdf.format(cell.getDateCellValue()));
							} else {
								cols.add(""
										+ df.format(cell.getNumericCellValue()));
							}
							break;
						case HSSFCell.CELL_TYPE_FORMULA:
							cols.add(cell.getCellFormula());
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							cols.add("");
							break;
						}
					}
				}

				data.add(cols);
			}
		} finally {
			is.close();
		}
		return data;
	}

	// 07版本
	public static List<List<String>> importEx(FileInputStream is,
			List<String> tlist, List<String> etypeList) throws Exception {
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		// Workbook xwb = StreamingReader.builder()
		// .rowCacheSize(100) // number of rows to keep in memory (defaults to
		// 10)
		// .bufferSize(1024) // buffer size to use when reading InputStream to
		// file (defaults to 1024)
		// .open(f);
		Workbook xwb = new XSSFWorkbook(is);
		// 读取第一张表格内容
		Sheet sheet = xwb.getSheetAt(0);
		// 定义 row、cell
		Row row;
		Cell cell = null;

		List<List<String>> data = new ArrayList<List<String>>();
		int cellTotal = sheet.getRow(sheet.getFirstRowNum())
				.getPhysicalNumberOfCells();

		// 解决隐藏列的问题
		List<Integer> orderList = new ArrayList<Integer>();
		for (int j = 0; j < tlist.size(); j++) {
			int num = 0;
			for (int i = 0; i < cellTotal; i++) {
				Cell hCell = sheet.getRow(0).getCell(i);
				if ((hCell.toString()).equals(tlist.get(j))) {
					num = 1;
				}
			}
			if (num == 0) { // 说明是隐藏列
				orderList.add(j);
			}
		}

		try {
			// 循环输出表格中的内容
			for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
				row = sheet.getRow(i);
				List<String> cols = new ArrayList<String>();
				String value = "";
				for (int j = row.getFirstCellNum(); j < cellTotal; j++) {
					// 通过 row.getCell(j).toString() 获取单元格内容，
					// cell = row.getCell(j).toString();
					if (orderList.contains(j)) {
						cols.add(j, "");
					}

					cell = row.getCell(j);

					if (null == cell) {
						value = "";
					} else {
						switch (cell.getCellType()) {
						// 数值型
						case Cell.CELL_TYPE_NUMERIC:
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								// 如果是date类型则 ，获取该cell的date值
								Date date = HSSFDateUtil.getJavaDate(cell
										.getNumericCellValue());
								SimpleDateFormat format = new SimpleDateFormat(
										"yyyy-MM-dd HH:mm:ss");
								value = format.format(date);
								;
							} else {// 纯数字
								BigDecimal big = new BigDecimal(
										cell.getNumericCellValue());
								value = big.toString();
								// 解决1234.0 去掉后面的.0
								if (null != value && !"".equals(value.trim())) {
									String[] item = value.split("[.]");
									if (1 < item.length && "0".equals(item[1])) {
										value = item[0];
									}
								}
							}
							break;
						// 字符串类型
						case Cell.CELL_TYPE_STRING:
							if (etypeList.get(j).equals("coro") && i != 0) {
								String ss = cell.getStringCellValue();
								int idxUl = ss.indexOf("-");
								value = ss;
								if (idxUl >= 0) {
									value = ss.substring(0, idxUl);
								}
							} else {
								value = cell.getStringCellValue().toString();
							}
							break;
						// 公式类型
						case Cell.CELL_TYPE_FORMULA:
							// 读公式计算值
							value = String.valueOf(cell.getNumericCellValue());
							if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
								value = cell.getStringCellValue().toString();
							}
							break;
						// 布尔类型
						case Cell.CELL_TYPE_BOOLEAN:
							value = " " + cell.getBooleanCellValue();
							break;
						// 空值
						case Cell.CELL_TYPE_BLANK:
							value = "";
							break;
						// 故障
						case Cell.CELL_TYPE_ERROR:
							value = "";
							break;
						default:
							value = cell.getStringCellValue().toString();
						}
					}

					// cell = getValues(row.getCell(j),j,orderList);
					cols.add("\"" + value + "\"");
				}
				data.add(cols);
			}
		} finally {
			is.close();
		}
		return data;
	}

	// 解决excel类型问题，获得数值
	public static String getValues(Cell cell, int j, List<Integer> orderList) {
		String value = "";
		if (orderList.contains(j)) {
			return value;
		}

		if (null == cell) {
			return value;
		}
		switch (cell.getCellType()) {
		// 数值型
		case Cell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				// 如果是date类型则 ，获取该cell的date值
				Date date = HSSFDateUtil
						.getJavaDate(cell.getNumericCellValue());
				SimpleDateFormat format = new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss");
				value = format.format(date);
				;
			} else {// 纯数字
				BigDecimal big = new BigDecimal(cell.getNumericCellValue());
				value = big.toString();
				// 解决1234.0 去掉后面的.0
				if (null != value && !"".equals(value.trim())) {
					String[] item = value.split("[.]");
					if (1 < item.length && "0".equals(item[1])) {
						value = item[0];
					}
				}
			}
			break;
		// 字符串类型
		case Cell.CELL_TYPE_STRING:
			value = cell.getStringCellValue().toString();
			break;
		// 公式类型
		case Cell.CELL_TYPE_FORMULA:
			// 读公式计算值
			value = String.valueOf(cell.getNumericCellValue());
			if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
				value = cell.getStringCellValue().toString();
			}
			break;
		// 布尔类型
		case Cell.CELL_TYPE_BOOLEAN:
			value = " " + cell.getBooleanCellValue();
			break;
		// 空值
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		// 故障
		case Cell.CELL_TYPE_ERROR:
			value = "";
			break;
		default:
			value = cell.getStringCellValue().toString();
		}
		if ("null".endsWith(value.trim())) {
			value = "";
		}
		return value;
	}

	public static String getValue(Cell cell) {
		String value = "";
		if (null == cell) {
			return value;
		}
		switch (cell.getCellType()) {
		// 数值型
		case Cell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				// 如果是date类型则 ，获取该cell的date值
				Date date = HSSFDateUtil
						.getJavaDate(cell.getNumericCellValue());
				SimpleDateFormat format = new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss");
				value = format.format(date);
				;
			} else {// 纯数字
				BigDecimal big = new BigDecimal(cell.getNumericCellValue());
				value = big.toString();
				// 解决1234.0 去掉后面的.0
				if (null != value && !"".equals(value.trim())) {
					String[] item = value.split("[.]");
					if (1 < item.length && "0".equals(item[1])) {
						value = item[0];
					}
				}
			}
			break;
		// 字符串类型
		case Cell.CELL_TYPE_STRING:
			value = cell.getStringCellValue().toString();
			break;
		// 公式类型
		case Cell.CELL_TYPE_FORMULA:
			// 读公式计算值
			value = String.valueOf(cell.getNumericCellValue());
			if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
				value = cell.getStringCellValue().toString();
			}
			break;
		// 布尔类型
		case Cell.CELL_TYPE_BOOLEAN:
			value = " " + cell.getBooleanCellValue();
			break;
		// 空值
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		// 故障
		case Cell.CELL_TYPE_ERROR:
			value = "";
			break;
		default:
			value = cell.getStringCellValue().toString();
		}
		if ("null".endsWith(value.trim())) {
			value = "";
		}
		return value;
	}

	// java map convert list
	public static LinkedHashMap testMapVoid(LinkedHashMap map) {
		// Map map = new HashMap();
		// map.put("a", "a1");
		// map.put("b", "b1");
		// map.put("c", "c1");

		// List listKey = new ArrayList();
		// List listValue = new ArrayList();
		// List rlist = new ArrayList();
		// Iterator it = map.keySet().iterator();
		// while (it.hasNext()) {
		// String key = it.next().toString();
		// listKey.add(key);
		// listValue.add(map.get(key));
		// }
		//
		// for(int i =0 ;i<listKey.size();i++){
		// rlist.add(listValue.get(i));
		// }
		// return rlist;

		LinkedHashMap<String, Map<String, String>> link_map = null;
		Set<String> set = map.keySet();
		List tlist = new ArrayList();
		List rlist = new ArrayList();
		LinkedHashMap rm = new LinkedHashMap();

		for (String key : set) {
			tlist.add(key);
			rlist.add(map.get(key));
		}
		rm.put("tlist", tlist);
		rm.put("vlist", rlist);
		return rm;
	}

	// 解决导出文件中文名乱码的问题
	public static void setFileDownloadHeader(HttpServletRequest request,
			HttpServletResponse response, String fileName) {
		final String userAgent = request.getHeader("USER-AGENT");
		try {
			String finalFileName = null;
			if (StringUtils.contains(userAgent, "MSIE")) {// IE浏览器
				finalFileName = URLEncoder.encode(fileName, "UTF8");
			} else if (StringUtils.contains(userAgent, "Mozilla")) {// google,火狐浏览器
				finalFileName = new String(fileName.getBytes(), "ISO8859-1");
			} else {
				finalFileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器
			}
			response.setHeader("Content-Disposition", "attachment; filename=\""
					+ finalFileName + "\"");// 这里设置一下让浏览器弹出下载提示框，而不是直接在浏览器中打开
		} catch (UnsupportedEncodingException e) {
		}
	}

	public static void main(String[] args) throws IOException {

		// FileOutputStream os = new FileOutputStream(new File("d:/test.xls"));
		// List<List<String>> data = new ArrayList<List<String>>();
		// List<String> s = new ArrayList<String>();
		// s.add("11");
		// s.add("22");
		// s.add("33");
		// data.add(s);
		// List<String> s1 = new ArrayList<String>();
		// s1.add("11");
		// s1.add("22");
		// s1.add("33");
		// data.add(s1);
		// exportToExcel(os, data);

		FileInputStream is = new FileInputStream(new File("d:/test.xls"));
		// List<List<String>> data = importFromExcel(is);
	}

	// --------------------------------------------------------------------------------------------------------------
	// 表头
	// public static final String[] tableHeader =
	// {"姓名","性别","年龄","电话","住址","公司"};
	// 创建工作本
	public static HSSFWorkbook WorkBook = new HSSFWorkbook();
	// 创建表
	public static HSSFSheet tableSheet = WorkBook.createSheet("Sheet1");

	// 表头的单元格个数目
	// public static final short cellNumber = (short)tableHeader.length;
	// 数据库表的列数
	// public static final int columNumber = tableHeader.length;

	/**
	 * 创建表头
	 * 
	 * @return
	 */
	public void createTableHeader(String[] tableHeader) {
		HSSFRow headerRow = tableSheet.createRow((short) 0);
		for (int i = 0; i < (short) tableHeader.length; i++) {
			HSSFCell headerCell = headerRow.createCell((short) i);
			headerCell.setCellValue(tableHeader[i]);
			headerCell.setCellStyle(getTitleStyle(WorkBook, tableSheet));
		}
	}

	/**
	 * 创建行
	 * 
	 * @param cells
	 * @param rowIndex
	 */
	public void createTableRow(List<List<String>> data) {

		// 创建数据行
		if (data != null) {
			for (int r = 0; r < data.size(); r++) {
				// 创建第r+1行
				HSSFRow row = tableSheet.createRow(r + 1);
				HSSFCellStyle style = getCellStyle(WorkBook);
				List<String> cols = data.get(r);
				for (int c = 0; c < cols.size(); c++) {
					// 创建第c个单元格
					HSSFCell cell = row.createCell(c);
					cell.setCellStyle(style);
					String oj = "";
					if (cols.get(c) != null) {
						oj = cols.get(c).toString();
					}
					cell.setCellValue(new HSSFRichTextString(oj));
				}
			}
		}
	}

	/**
	 * 导出表格
	 * 
	 * @param sheet
	 * @param os
	 * @return
	 * @throws IOException
	 */
	public void expord(OutputStream os) throws IOException {
		tableSheet.setGridsPrinted(true);
		HSSFFooter footer = tableSheet.getFooter();
		footer.setRight("Page " + HSSFFooter.page() + " of "
				+ HSSFFooter.numPages());
		// tableSheet.setColumnHidden(0,true); //隐藏ID列

		WorkBook.write(os);
	}

	/**
	 * 设置头部样式
	 * 
	 * @return HSSFCellStyle
	 */
	public static HSSFCellStyle getTitleStyle(HSSFWorkbook WorkBook,
			HSSFSheet tableSheet) {
		HSSFCellStyle style = WorkBook.createCellStyle();
		HSSFFont font = WorkBook.createFont();

		tableSheet.setColumnWidth((short) 7, (short) 7000);
		tableSheet.setColumnWidth((short) 8, (short) 7000);
		tableSheet.setColumnWidth((short) 11, (short) 5000);
		tableSheet.setColumnWidth((short) 12, (short) 5000);

		font.setFontName("Arial");
		font.setFontHeightInPoints((short) 10);// 设置字体大小
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFillForegroundColor(HSSFColor.LAVENDER.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setWrapText(true);
		style.setFont(font);
		return style;
	}

	/**
	 * 设置表格格样式
	 * 
	 * @return HSSFCellStyle
	 */
	public static HSSFCellStyle getCellStyle(HSSFWorkbook WorkBook) {
		HSSFCellStyle style = WorkBook.createCellStyle();
		HSSFFont font = WorkBook.createFont();
		font.setFontHeightInPoints((short) 10);// 设置字体大小
		style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setFont(font);
		return style;
	}

	public static JSONArray parseExcel(OutputStream os) throws IOException {
		DeferredFileOutputStream baos = (DeferredFileOutputStream) os;
		// create input stream from baos
		InputStream is = new FileInputStream(baos.getFile());
		// 构造 XSSFWorkbook 对象，strPath 传入文件路径
		XSSFWorkbook xwb = new XSSFWorkbook(is);
		os.close();
		// 读取第一张表格内容
		XSSFSheet sheet = xwb.getSheetAt(0);

		JSONArray data = new JSONArray();
		// 循环输出表格中的内容
		for (int i = sheet.getFirstRowNum(); i < sheet
				.getPhysicalNumberOfRows(); i++) {
			XSSFRow row = sheet.getRow(i);// 定义 row
			JSONArray cols = new JSONArray();
			for (int j = row.getFirstCellNum(); j < row
					.getPhysicalNumberOfCells(); j++) {
				// 通过 row.getCell(j).toString() 获取单元格内容，
				// cell = row.getCell(j).toString();
				String cell = getValue(row.getCell(j));
				cols.add(cell);
			}
			data.add(cols);
		}
		return data;
	}
}